package com.own.component.util.database;

import com.alibaba.druid.sql.ast.statement.SQLColumnDefinition;
import com.alibaba.druid.sql.ast.statement.SQLPrimaryKey;
import com.own.component.util.database.base.BaseDatabaseFieldMapping;
import com.own.component.util.database.base.BaseDatabaseProperty;
import com.own.component.util.database.base.model.DatabaseEntryField;
import com.own.component.util.database.em.DatabaseExceptionEnum;
import com.own.component.util.database.exception.DatabaseException;
import com.own.component.util.database.model.DatabaseConfigModel;
import com.own.component.util.database.model.TableColumnModel;
import com.own.component.util.database.model.TableSchema;
import lombok.Getter;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collection;
import java.util.Collections;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Objects;
import java.util.Set;
import java.util.regex.Pattern;
import java.util.stream.Collectors;

/**
 * DatabaseUtil
 *
 * @author chenxueli
 * @date 2022-10-15 14:03:10
 */
@Slf4j
public class DatabaseUtil {

    /**
     * 数据库类型信息
     */
    private final BaseDatabaseProperty property;
    /**
     * 字段名称信息
     */
    private final DatabaseEntryField field;
    /**
     * 数据库连接配置信息
     */
    private final DatabaseConfigModel config;
    /**
     * 自定义的字段映射信息
     */
    private final Map<String, BaseDatabaseFieldMapping> customFieldMapping;
    /**
     * sql解析的异常信息
     */
    @Getter
    private final List<Throwable> sqlResolveErrorList = new ArrayList<>();
    /**
     * 连接信息
     */
    private Connection connection;

    /**
     * 配置构造方法
     *
     * @param url      数据库连接地址
     * @param username 用户名
     * @param password 密码
     * @param property 数据库类型信息
     */
    public DatabaseUtil(String url, String username, String password, BaseDatabaseProperty property) {
        this(
                DatabaseConfigModel.builder().url(url).username(username).password(password).build(),
                property
        );
    }

    /**
     * 配置构造方法
     *
     * @param config   数据库连接配置信息
     * @param property 数据库类型信息
     */
    public DatabaseUtil(DatabaseConfigModel config, BaseDatabaseProperty property) {
        this(config, property, null);
    }

    /**
     * 配置构造方法
     *
     * @param config   数据库连接配置信息
     * @param property 数据库类型信息
     * @param mapping  映射关系
     */
    public DatabaseUtil(DatabaseConfigModel config, BaseDatabaseProperty property, Map<String, BaseDatabaseFieldMapping> mapping) {
        if (config == null || property == null) {
            throw new DatabaseException(DatabaseExceptionEnum.CONFIG_NULL);
        }
        // 正则获取数据库地址中的数据库名称

        // 定义匹配正则表达式
        if (StringUtils.isNotBlank(config.getUrl())) {
            var pattern = Pattern.compile(property.databaseRegex());
            var matcher = pattern.matcher(config.getUrl());
            if (matcher.find()) {
                config.setDatabase(matcher.group("dbName"));
            }
        }
        this.property = property;
        this.field = property.getField();
        this.config = config;
        this.customFieldMapping = mapping;
    }


    /**
     * 构造函数
     *
     * @param url      数据库连接地址
     * @param username 用户名
     * @param password 密码
     * @param property 数据库类型信息
     */
    public static DatabaseUtil instance(String url, String username, String password, BaseDatabaseProperty property) {
        return new DatabaseUtil(url, username, password, property);
    }

    /**
     * 构造函数
     *
     * @param config   数据库连接配置信息
     * @param property 数据库类型信息
     * @return DatabaseUtil
     */
    public static DatabaseUtil instance(DatabaseConfigModel config, BaseDatabaseProperty property) {
        return new DatabaseUtil(config, property);
    }

    /**
     * 构造函数
     *
     * @param config   数据库连接配置信息
     * @param property 数据库类型信息
     * @param mapping  映射关系
     * @return DatabaseUtil
     */
    public static DatabaseUtil instance(DatabaseConfigModel config, BaseDatabaseProperty property, Map<String, BaseDatabaseFieldMapping> mapping) {
        return new DatabaseUtil(config, property, mapping);
    }

    /**
     * 将获得的数据库与java的链接返回（返回的类型为Connection）
     *
     * @return 数据库连接
     */
    protected Connection open() {
        if (connection != null) {
            return connection;
        }
        var url = StringUtils.isNotBlank(config.getUrl())
                ? config.getUrl()
                : property.connectionUrl().replace("{host}", config.getHost()).replace("{port}", config.getPort()).replace("{database}", config.getDatabase());
        try {
            // 加载驱动程序
            Class.forName(property.driver());
            // 获得数据库的连接
            connection = DriverManager.getConnection(url, config.getUsername(), config.getPassword());
            return connection;
        } catch (ClassNotFoundException | SQLException e) {
            log.error("获取数据库连接失败", e);
        }
        throw new DatabaseException(DatabaseExceptionEnum.LINK_ERROR);
    }

    /**
     * 关闭数据库连接
     */
    protected void close() {
        if (connection != null) {
            try {
                connection.close();
                connection = null;
            } catch (SQLException e) {
                throw new DatabaseException(DatabaseExceptionEnum.CLOSE_ERROR);
            }
        }
    }

    /**
     * 获取所有表的名称
     *
     * @return 所有表的信息
     */
    public Set<String> listTableNames() {
        var connection = open();
        // 获取表信息
        var tableList = runTableInfo(connection, property.tablesSql(), config.getDatabase());
        close();
        return tableList.stream().map(TableSchema::getTableName).collect(Collectors.toSet());
    }

    /**
     * 获取所有表的信息
     *
     * @return 所有表的信息
     */
    public List<TableSchema> listTablesInfo() {
        var connection = open();
        // 获取表信息
        var tableList = runTableInfo(connection, property.tablesSql(), config.getDatabase());
        // 查询出所有表的字段信息
        var columnMap = runTableColumnInfo(connection, property.tablesColumnSql(), config.getDatabase());
        // 组装数据
        tableList.parallelStream().forEach(table -> {
            var columnList = columnMap.get(table.getTableName());
            table.setList(columnList == null ? new ArrayList<>() : columnList);
        });
        // 关闭连接信息
        close();
        return tableList;
    }

    /**
     * 获取多个表的信息
     *
     * @param tableNames 表名列表
     * @return 所有表的信息
     */
    public List<TableSchema> listTablesInfo(String... tableNames) {
        return listTablesInfo(Arrays.asList(tableNames));
    }

    /**
     * 获取多个表的信息
     *
     * @param tableNameList 表名列表
     * @return 所有表的信息
     */
    public List<TableSchema> listTablesInfo(Collection<String> tableNameList) {
        if (tableNameList.isEmpty()) {
            return new ArrayList<>();
        }
        var connection = open();
        // 获取表信息
        var tableList = runTableInfo(connection, property.tablesSql(tableNameList), config.getDatabase());
        // 查询出所有表的字段信息
        var columnMap = runTableColumnInfo(connection, property.tablesColumnSql(tableNameList), config.getDatabase());
        // 组装数据
        tableList.parallelStream().forEach(table -> {
            var columnList = columnMap.get(table.getTableName());
            table.setList(columnList == null ? new ArrayList<>() : columnList);
        });
        // 关闭连接信息
        close();
        return tableList;
    }

    /**
     * 获取单个表的信息
     *
     * @param tableName 表名
     * @return 表信息
     */
    public TableSchema getTableInfo(String tableName) {
        var list = listTablesInfo(tableName);
        if (list.isEmpty()) {
            throw new DatabaseException(DatabaseExceptionEnum.TABLE_NOT_EXIST);
        }
        return list.get(0);
    }

    /**
     * 根据sql获取表格信息
     *
     * @param sql sql语句
     * @return 表格信息
     */
    public TableSchema getTableInfoBySql(String sql) {
        var tableSchema = new TableSchema();
        try {
            // 解析sql
            var parser = property.createTableParser(sql);
            var statement = parser.parseCreateTable();
            tableSchema.setTableName(formatContentFormSql(statement.getTableName()));
            if (statement.getComment() != null) {
                tableSchema.setComment(formatContentFormSql(statement.getComment().toString()));
            }
            var columnList = statement.getTableElementList().stream().map(element -> {
                var column = new TableColumnModel();
                // 主键约束
                if (element instanceof SQLPrimaryKey primaryKey) {
                    var fieldName = primaryKey.getColumns().get(0).toString();
                    column.setColumnName(formatContentFormSql(fieldName));
                } else if (element instanceof SQLColumnDefinition definition) {
                    column.setColumnName(formatContentFormSql(definition.getColumnName()));
                    if (definition.getComment() != null) {
                        column.setComment(formatContentFormSql(definition.getComment().toString()));
                    }
                    var dataType = definition.getDataType();
                    // 设置映射信息
                    column.setMapping(getMapping(dataType.getName()));
                    try {
                        var length = dataType.getArguments().get(0).toString();
                        column.setLength(Long.parseLong(length));
                    } catch (Exception ignored) {
                    }
                }
                return column;
            }).toList();
            tableSchema.setList(columnList);
        } catch (Exception e) {
            // 保存异常信息
            sqlResolveErrorList.add(e);
            return null;
        }
        return tableSchema;
    }

    /**
     * 根据sql获取表信息
     *
     * @param sql sql语句
     * @return 表信息
     */
    public List<TableSchema> listTablesInfoBySql(String sql) {
        // 清除异常消息列表
        sqlResolveErrorList.clear();
        // 处理数据
        var indexList = new ArrayList<Integer>();
        var ch = ';';
        sql = sql.trim();
        if (!sql.endsWith(";")) {
            sql = sql + ";";
        }
        var index = sql.indexOf(ch);
        while (index != -1) {
            indexList.add(index);
            index = sql.indexOf(ch, index + 1);
        }
        // 如果没有分号，直接返回
        if (indexList.isEmpty()) {
            return Collections.singletonList(getTableInfoBySql(sql));
        }
        var index1 = 0;
        var tableList = new ArrayList<TableSchema>();
        for (var i = 0; i < indexList.size(); i++) {
            var index2 = indexList.get(i);
            // 获取这一段的内容
            var tempSql = sql.substring(index1, index2 + 1);
            if (tempSql.startsWith(";")) {
                tempSql = tempSql.substring(1);
            }
            // 判断是否有效
            if (StringUtils.countMatches(tempSql, "'") % 2 == 1 || StringUtils.countMatches(tempSql, "`") % 2 == 1) {
                // 只有不是最后一个时候才需要返回
                if (i != indexList.size() - 1) {
                    continue;
                }
            }
            index1 = index2;
            tableList.add(getTableInfoBySql(tempSql));
        }
        return tableList.stream().filter(Objects::nonNull).toList();
    }

    /**
     * 获取表信息
     *
     * @param connection 数据库连接
     * @param sql        sql语句
     * @param array      参数
     * @return 结果集
     */
    private List<TableSchema> runTableInfo(Connection connection, String sql, String... array) {
        var tableList = new ArrayList<TableSchema>();
        try {
            var result = run(connection, sql, array);
            while (result.next()) {
                var table = new TableSchema();
                table.setTableName(result.getString(field.getTableName()));
                table.setComment(result.getString(field.getTableComment()));
                tableList.add(table);
            }
        } catch (SQLException e) {
            throw new DatabaseException(DatabaseExceptionEnum.RESOLVE_ERROR);
        }
        return tableList;
    }

    /**
     * 获取表信息
     *
     * @param connection 数据库连接
     * @param sql        sql语句
     * @param array      参数
     * @return 结果集
     */
    private Map<String, List<TableColumnModel>> runTableColumnInfo(Connection connection, String sql, String... array) {
        var map = new HashMap<String, List<TableColumnModel>>();
        try {
            var result = run(connection, sql, array);
            while (result.next()) {
                var column = new TableColumnModel();
                var tableName = result.getString(field.getTableName());
                // 向map中保存信息
                map.computeIfAbsent(tableName, k -> new ArrayList<>()).add(column);
                // 获取相关的属性信息
                column.setColumnName(result.getString(field.getColumnName()));
                column.setComment(result.getString(field.getColumnComment()));
                column.setMapping(getMapping(result.getString(field.getType())));
                column.setLength(result.getLong(field.getLength()));
                column.setIsNull(field.getNullHandler().apply(result.getString(field.getIsNull())));
                column.setDefaultValue(result.getString(field.getDefaultValue()));
                column.setIsPrimaryKey(field.getPrimaryKeyHandler().apply(result.getString(field.getIsPrimaryKey())));
            }
        } catch (SQLException e) {
            throw new DatabaseException(DatabaseExceptionEnum.RESOLVE_ERROR);
        }
        return map;
    }

    /**
     * 执行sql获取数据
     *
     * @param sql sql语句
     * @return 结果集
     */
    private ResultSet run(String sql) {
        var connection = open();
        try {
            var statement = connection.prepareStatement(sql);
            return statement.executeQuery();
        } catch (Exception e) {
            throw new DatabaseException(DatabaseExceptionEnum.SQL_EXECUTE_ERROR);
        } finally {
            close();
        }
    }

    /**
     * 执行sql获取数据
     *
     * @param sql   sql语句
     * @param array 参数
     * @return 结果集
     */
    private ResultSet run(String sql, String... array) {
        var connection = open();
        try {
            var statement = connection.prepareStatement(sql);
            for (int i = 0; i < array.length; i++) {
                statement.setString(i + 1, array[i]);
            }
            return statement.executeQuery();
        } catch (Exception e) {
            throw new DatabaseException(DatabaseExceptionEnum.SQL_EXECUTE_ERROR);
        } finally {
            close();
        }
    }

    /**
     * 执行sql获取数据
     *
     * @param connection 数据库连接
     * @param sql        sql语句
     * @param array      参数
     * @return 结果集
     */
    private ResultSet run(Connection connection, String sql, String... array) {
        try {
            var statement = connection.prepareStatement(sql);
            for (int i = 0; i < array.length; i++) {
                statement.setString(i + 1, array[i]);
            }
            return statement.executeQuery();
        } catch (Exception e) {
            throw new DatabaseException(DatabaseExceptionEnum.SQL_EXECUTE_ERROR);
        }
    }

    /**
     * 获取mapping信息
     *
     * @param type 字段类型
     * @return 映射信息
     */
    private BaseDatabaseFieldMapping getMapping(String type) {
        var mapping = property.getFieldMappingAndDefault(type);
        if (mapping == null && customFieldMapping != null) {
            mapping = customFieldMapping.get(type);
        }
        return mapping == null ? property.defaultFieldType() : mapping;
    }

    /**
     * 格式化从sql获取到的字段，备注信息
     *
     * @param content 内容
     * @return 格式化后的内容
     */
    private String formatContentFormSql(String content) {
        // 去掉首尾的'`
        return content
                .trim()
                .replaceAll("^['|`]+(.*)$", "$1")
                .replaceAll("^(.*)['|`]+$", "$1");
    }

}
